<?php
namespace DB;
$dbConfig=[
    '$dbType'=>'mysql',
    'host'=>'localhost',
    'dbName'=>'phpcn',
    'user'=>'root',
    'password'=>'123456'
];
$dsn="{$dbConfig['$dbType']}:host={$dbConfig['host']};dbname={$dbConfig['dbName']}";  // 配置DSN
class db{
    public $pdo;
    public $sql;
    public function __construct($dsn,$username,$userpwd){
        $this->pdo= new \PDO($dsn,$username,$userpwd);
    }
    private function substrPos($str,$operator){  //取指定符号之前字符方法
        $str = substr($str,0,strrpos($str,$operator));
        return $str;
    }
    private function substriPos($str,$operator){  //取指定符号之后字符方法
        $str = substr($str,strripos($str,$operator)+1);
        return $str;
    }
    private function substrDel($str,$del){  //删除字符串末尾多余字符方法
        $str=substr($str,0,strlen($str)-$del);
        return $str;
    }
    private function getOperator($con){
        $operatorArr=['=','>','>=','<','<=','!='];  // 条件操作符集
        //拆分WHERE为 字段 和 值，为预处理做准备
        $whereParam=''; // WHERE的字段
        $whereValue=''; // WHERE的条件
        $operator=''; //返回操作符
        $existOperator=0; // 判断是否存可执行条件符号
        for($i=0;$i<count($operatorArr);$i++){
            if(strstr($con,$operatorArr[$i])){
                $whereParam=$this->substrPos($con,$operatorArr[$i]); // 获得拆分后的字段
                $whereValue=$this->substriPos($con,$operatorArr[$i]); // 获得拆分后的值
                $operator = $operatorArr[$i];   // 获取操作符
                $existOperator=1;  // 匹配到操作符，该条件成立
            }
        }
        if(!$existOperator){
            echo '暂时不支持该查询操作';
        }
        $where=$whereParam.$operator.':'.$whereParam;  //形参重写where条件
        $con = [
            $where,$whereParam,$whereValue
        ]; //将 形参条件，条件字段，条件值以数组形式返回
        return $con;
    }
    public function dbOperation($operation,$fields,$values,$table,$where='',$order='',$limit='')  //查询语句
    {
        $time = date('Y-m-d H:i:s'); //设置日期格式
        if(empty($table) || empty($operation)){
            echo '请输入要操作的表';
            exit;
        }
        switch(strtoupper($operation)){
            case 'UPDATE':
                if(is_array($fields) && is_array($values)) {
                    if (count($fields) != count($values)) {
                        echo '字段和值不匹配';
                        exit;
                    }else if(empty($where)){
                        echo '该操作必须包含条件参数';
                        exit;
                    }else{
                        list($where,$whereParam,$whereValue) = $this->getOperator($where);  // 处理之后的WHERE语句，返回WHERE形参，WHERE字段，WHERE值
                    }
                }
                $updateParam = '`update`=:time'; //设置时间形参
                $this->sql=$operation.' `'.$table.'` SET ';
                foreach($fields as $field_v){
                    $this->sql.='`'.$field_v.'`=:'.$field_v.', ';  //设置形参，为预处理做准备 形参为:$field_v
                }
                $this->sql.=$updateParam;
                $this->sql.=' WHERE '.$where;
                ///设置查询条件
                $stmt=$this->pdo->prepare($this->sql);  //预处理SQL语句
                foreach($values as $v_k => $v_v){
                    $stmt->bindValue(":{$fields[$v_k]}",$v_v);  //循环绑定字段和值
                }
                $stmt->bindValue(':time',$time);  //绑定 TIME 更新时间
                $stmt->bindValue(":{$whereParam}",$whereValue);  //绑定 WHERE 条件
                $num=$stmt->execute();
                if($num > 0){
                    echo '<br>更新操作成功';
                }
            $this->sql=null; //释放SQL语句
            break;

            case 'DELETE':
                if(empty($where)){
                    echo '该操作必须包含条件参数';
                    exit;
                }else{
                    list($where,$whereParam,$whereValue) = $this->getOperator($where);  // 处理之后的WHERE语句，返回WHERE形参，WHERE字段，WHERE值
                }
                $this->sql=$operation.' FROM `'.$table.'` '.'WHERE '.$where;
                $stmt=$this->pdo->prepare($this->sql);
                $num=$stmt->execute([":{$whereParam}"=>$whereValue]);  //绑定WHERE条件
                if($num>0){
                    echo '<br>删除操作成功';
                }
            $this->sql=null; //释放SQL语句
            break;

            case 'INSERT':
                $insertField='';  // 处理添加字段
                $insertParam=''; // 设置添加值形参
                $dateField = '`date`'; //设置时间字段
                $dateParam = ':time'; //设置时间形参
                for($i=0;$i<count($fields);$i++){
                    $insertField.='`'.$fields[$i].'`,';
                    $insertParam.=':'.$fields[$i].',';
                }  //循环添加字段形参
                $insertField.=$dateField;
                $insertParam.=$dateParam;
                $this->sql=$operation.' INTO `'.$table.'` ('.$insertField.') VALUES ('.$insertParam.')';
                $stmt=$this->pdo->prepare($this->sql);
                foreach($values as $v_k => $v_v){
                    $stmt->bindValue(":{$fields[$v_k]}",$v_v);
                }
                $stmt->bindValue(":time",$time);  //绑定 日期 字段
                $num = $stmt->execute();
                if($num>0){
                    echo '<br>添加操作成功';
                }
            $this->sql=null; //释放SQL语句
            break;

            case 'SELECT' :
                $this->sql='SELECT ';
                if($fields[0] != '*'){  // 判断是否查询所有字段
                    foreach($fields as $field){
                        $this->sql.='`'.$field.'`,';
                    }
                    $this->sql=$this->substrDel($this->sql,1);  //删除末尾多余','
                }//添加查询字段
                else {
                    $this->sql.='*';
                }
                $this->sql.=' FROM `'.$table.'` ';
                if(!empty($where))
                {
                    list($where,$whereParam,$whereValue) = $this->getOperator($where);
                    $this->sql.='WHERE '.$where;
                }
                empty($order) ?: $this->sql.=' ORDER BY '.$order;
                empty($limit) ?: $this->sql.=' LIMIT '.$limit;
                $stmt=$this->pdo->prepare($this->sql);
                echo $this->sql.'<br>';
                $stmt->bindValue($whereParam,$whereValue);//绑定WHERE条件
                $stmt->execute();
//                echo '<br><br>预处理语句生成<br><br>';
//                $stmt->debugDumpParams();  //预处理语句调试
//                echo '<br>';
//                print_r($stmt->errorInfo());  // 错误信息捕捉
                echo '<br><br>结果：<br>';
                $stmt->setFetchMode(\PDO::FETCH_ASSOC);
                $result=$stmt->fetchAll();
                foreach($result as $r_v){
                    print_r($r_v);
                    echo '<hr>';
                }
            $this->sql=null; //释放SQL语句
            break;
            default:
            break;
        }
    }

    public function __destruct(){
        $this->pdo=null;
        echo '<p>连接已断开</p>';
    }
}
$db = new db($dsn,$dbConfig['user'],$dbConfig['password']);  //生成对象时直接添加 dsn user userpwd
if($db->pdo){
    echo '<h2>连接成功</h2>';
}
//$db->dbOperation('UPDATE',array('name','from','skill','level'),array('乔峰','契丹','降龙十八掌','5'),'wuxia','id=14');
//$db->dbOperation('INSERT',array('name','from','skill','level'),array('虚竹','逍遥派','小无相功','8'),'wuxia');
//$db->dbOperation('SELECT',array('name','from','skill','level'),'','wuxia','level>7','level ASC','10');
$db->dbOperation('DELETE','','','wuxia','id=13');
